#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use utf8;
use Encode;

# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});

my $fichier=shift;
my @temp=split(/\\/,$fichier);
my $nom_fichier=$temp[2];

my $worksheet;
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

foreach($workbook->worksheets()){
    $worksheet=$_;
	my ($row_min,$row_max) = $worksheet->row_range();
	my ($col_min, $col_max) = $worksheet->col_range();

	my $micro; #num microcebe
	my %test2; #groupe=>{nom_val=>(val,ref)}
	my $date_t;
	my @images;
	my $value;
	my $type; 
	my $vivo;
	
	for my $row ($row_min .. $row_max){ #parcours lignes
		my $cont=$worksheet->get_cell($row,0);
		if($cont){$value=$cont->value(); }
		
		my $case=$worksheet->get_cell(0,1); #récupération numero microcebe
		if($case){ 
			my $mic=$case->value(); 
			my @tab1=split('-',$mic);
			$micro=$tab1[0];
		}
		
		if($value eq "Date IRM (jj/mm/aaaa)"){ #récupération date du test
			my $cont2=$worksheet->get_cell($row,1);
			if($cont2){ $date_t=$cont2->value(); }
		}
		
		elsif($value eq "in_vivo"){ #récupération in_vivo
			my $cont2=$worksheet->get_cell($row,1);
			if($cont2){ 
				my $vie=$cont2->value(); 
				if($vie eq "oui"){$vivo='true';}
				else{$vivo='false';}
			}
		}
		
		elsif($value eq "Visualisation coupes de références (Image-rot,ima)"){
			for my $col ($col_min .. $col_max){ #parcours colonnes
				#récupération nom des images
				my $row2=$row+13;
				my $row3=$row+25;
				my $cont2=$worksheet->get_cell($row2,$col);
				
				if($cont2 and $cont2->value() ne ''){
					push(@images,$cont2->value());
				}
				my $cont3=$worksheet->get_cell($row3,$col);
				if($cont3 and $cont3->value() ne ''){
					push(@images,$cont3->value());
				}
			}	
		}
		elsif($value eq "Scoring semiquantitatif basé sur une estimation visuelle (Image-rot.ima)"){
			my %test;
			my $row2=$row+2;
			my $cont2=$worksheet->get_cell($row2,0);
			
			while($cont2 and $cont2->value() ne 'Mesure sur la base de ROIs (Image-rot512.ima)' and $cont2->value() ne ''){
				my @tab;
				my @tab2;
				my $temp=$cont2->value();
				my $cont3 = $worksheet->get_cell($row2,5); #récupération du score
				push(@tab,$cont3->value());
				$test{$temp} = \@tab;
				
				my $cont4 = $worksheet->get_cell($row2,6); #valeurs avec références
				if($cont4 and $cont4->value() ne ''){
					my $temp2 = $cont4->value();
					my $cont5 = $worksheet->get_cell($row2,7); #score
					push(@tab2,$cont5->value());
					my $cont6 = $worksheet->get_cell($row2,8); #référence
					push(@tab2,$cont6->value());	
					$test{$temp2} = \@tab2;
				}
				
				$row2++;
				$cont2=$worksheet->get_cell($row2,0);
			}
			$test2{$value}={%test};
		}
		
		elsif($value eq "Mesure sur la base de ROIs (Image-rot512.ima)"){
			my %test;
			my $row1=$row+1;
			my $row2=$row+2;
			my $cont2=$worksheet->get_cell($row2,0);
			my $type1=$worksheet->get_cell($row1,3);
			my $type=$type1->value();
			my @tab1=split('-',$type);
			$type=$tab1[1];
			while($cont2 and $cont2->value() ne "Mesure d'épaisseurs (corticales, splenium…) (Image-rot.ima)" and $cont2->value() ne ''){
				my @tab;
				my $temp=$cont2->value();
				$temp="$temp $type";
				my $cont3 = $worksheet->get_cell($row2,4); #récupération du score
				push(@tab,$cont3->value());
				my $cont4 = $worksheet->get_cell($row2,5); #récupération de la référence
				if($cont4){push(@tab,$cont4->value());}
				$test{$temp} = \@tab;
				
				$row2++;
				$cont2=$worksheet->get_cell($row2,0);
			}
			
			$test2{$value}={%test};
			
			#recherche si irm varian ou brucker
			my $row3 = $row+1;
			my $cont5=$worksheet->get_cell($row3,3);	
			my $temp=$cont5->value();
			my @tab2=split('-',$temp);
			$type=$tab2[1];
		}
		
		elsif($value eq "Mesure d'épaisseurs (corticales, splenium…) (Image-rot.ima)"){
			my %test;
			my $row2=$row+2;
			my $cont2=$worksheet->get_cell($row2,2);
			my $temp;
			while($cont2 and $cont2->value() ne ''){
				$temp = $cont2->value();
				my @tab;
				my $cont3 = $worksheet->get_cell($row2,3); #récupération du score
				push(@tab,$cont3->value());
				my $cont4 = $worksheet->get_cell($row2,4); #récupération de la référence
				if($cont4){push(@tab,$cont4->value());}
				$test{$temp} = \@tab;
				
				$row2++;
				$cont2=$worksheet->get_cell($row2,2);
			}
			$test2{$value}={%test};
		}
	}
	
	#~ for my $i (sort keys %test2){
		#~ print "$i: "; 
		#~ for my $j (sort keys %{$test2{$i}}){
			#~ print "$j: "; 
			#~ print "score: $test2{$i}{$j}[0] ";
		
			#~ if($test2{$i}{$j}[1]){
				#~ print "reference: $test2{$i}{$j}[1]";
			#~ }
			#~ print "\n";
		#~ }
		#~ print "\n";
	#~ }
		
	# ************************************* récupération id_animal ************************************* #
	my $id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal=(E'$micro')");
	
	# ************************************* récupération id_test ************************************* #
	#pb: on peut avoir plusieurs id_test a une meme date
	my @tests;
	my $prep_test=$dbh->prepare("SELECT id_test FROM Test,type_test WHERE date_test=(E'$date_t') AND type_test.label='IRM' AND Test.id_type_test=type_test.id_type_test");
	$prep_test->execute();
	while(my @ref=$prep_test->fetchrow_array()){
		push(@tests,$ref[0]);
	}
	$prep_test->finish();
	
	# ************************************* récupération id_res ************************************* #
	my $id_res;
	my $prep_id_res=$dbh->prepare("SELECT irm.id_res FROM resultat_test,irm WHERE id_test=? AND id_animal=$id_anim AND irm.in_vivo='$vivo' AND resultat_test.id_res=irm.id_res");
	foreach(@tests){
		$prep_id_res->execute($_);
		while(my @ref=$prep_id_res->fetchrow_array()){
			$id_res=$ref[0];
		}
		$prep_id_res->finish();
	}
	
	# ************************************* Création mesure_irm ************************************* #
	for my $i (sort keys %test2){
		my $groupe=encode_utf8($i);
		my $groupe2=$dbh->quote($groupe);
		for my $j (sort keys %{$test2{$i}}){
			my $nom=encode_utf8($j);
			my $nom2=$dbh->quote($nom);
			my $mesure;
			my $temp=encode_utf8($test2{$i}{$j}[0]);
			$mesure=$dbh->selectrow_array("SELECT nom_mesure FROM mesure_IRM WHERE nom_mesure=$nom2 AND id_res=$id_res AND groupe_mesure=$groupe2");
			
			if(not $mesure){
				my $score=encode_utf8($test2{$i}{$j}[0]);
				if($test2{$i}{$j}[1]){
					my $ref=encode_utf8($test2{$i}{$j}[1]);
					$dbh->do("INSERT INTO Mesure_IRM(id_res,nom_mesure,valeur_mesure,valeur_seuil,groupe_mesure) VALUES ($id_res,$nom2,'$score','$ref',$groupe2)");
				}
				else{
					$dbh->do("INSERT INTO Mesure_IRM(id_res,nom_mesure,valeur_mesure,groupe_mesure) VALUES ($id_res,$nom2,'$score',$groupe2)");
				}
			}
		}
	}
	
	#~ #ajout de la fiche à irm
	$dbh->do("UPDATE IRM SET fiche_irm='$nom_fichier' WHERE id_res=$id_res");
	
	# ************************************* Création photo_irm ************************************* #
	
	foreach my $i(@images){
		$dbh->do("INSERT INTO photo_irm(id_res,nom_photo) VALUES ($id_res,'$i')");
	}
	last if $_->get_name() eq 'Feuil1';
}
    
    
#~ $dbh->disconnect();
